import re
import os


def extract_table_info(sql_content):
    # 正则表达式模式
    create_table_pattern = r'CREATE\s+TABLE\s+([^\s(]+)\s*\(([\s\S]*?)\);'
    comment_on_table_pattern = r'COMMENT\s+ON\s+TABLE\s+([^\s]+)\s+IS\s+"?([^"]*)"?;'

    # 查找所有的`CREATE TABLE`语句
    create_table_matches = re.finditer(create_table_pattern, sql_content, re.IGNORECASE | re.DOTALL)

    # 结果列表
    results = []

    for match in create_table_matches:
        # 提取`CREATE TABLE`信息
        table_name = match.group(1).strip('"')
        create_table_info = match.group()

        # 查找紧随其后的`COMMENT ON TABLE`
        start_index = match.end()
        comment_on_table_match = re.search(comment_on_table_pattern, sql_content[start_index:], re.IGNORECASE)

        if comment_on_table_match:
            # 提取`COMMENT ON TABLE`信息
            comment_on_table_info = comment_on_table_match.group(0)
            results.append((create_table_info, comment_on_table_info))

    return results


# 指定目录路径
directory = 'D:/06git/01python/02vanna/02导入训练sql数据/smartlight/data'

# 遍历目录中的所有文件
for filename in os.listdir(directory):
    if filename.endswith('.sql'):
        filepath = os.path.join(directory, filename)
        with open(filepath, 'r', encoding='utf-8') as file:
            content = file.read()

            # 提取`CREATE TABLE`和`COMMENT ON TABLE`信息
            table_info_list = extract_table_info(content)

            # 打印结果
            for create_table_info, comment_on_table_info in table_info_list:
                print(f"{create_table_info}")
                print(f"{comment_on_table_info}\n")